#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use db_parameters;
use ortho_species;

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_pairwise

	Description:
		Obtains all pairwise combinations of relationships predicted
		the different protocols
	Populates DB tables:
		orthologs.pairwise
		orthologs.pairwise_overlap
		orthologs.pairwise_prot_id
	Uses
		orthologs.ortholog_sets
		taxon.gene_longest_transcript

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#-----------------------------------------------------------------------------------------
#	Get DB parameters
#
use db_parameters;
use ortho_species;
#my @db_param = get_db_parameters();
#
#	Get DB parameters
#-----------------------------------------------------------------------------------------






#
# connect to genome database
#
my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);

$dbh->do ("set work_mem=160384");

	my $sql_cmd;



#_________________________________________________________________________________________
#
#
#	populate_pairwise_relationships
#
#_________________________________________________________________________________________
sub populate_pairwise_relationships($$)
{
	my ($dbh, $prot_id) = @_;
	
	# table name
	my $table_name = "pairwise";
	$table_name .= '_prot_id' if $prot_id;

	# extra fields if prot_id
	my $prot_id_fields1 = $prot_id ? ',prot_id1, prot_id2' : '';
	my $prot_id_fields2 = $prot_id ? ',o1.prot_id, o2.prot_id' : '';
	my $prot_id_fields3 = $prot_id ? 'o1.prot_id, o2.prot_id' : 'o1.gene_id, o2.gene_id';
	
	my $sql_cmd =<<"PL/SQLCMD";
    TRUNCATE TABLE orthologs.$table_name;
    VACUUM ANALYSE orthologs.$table_name;
    INSERT INTO
            orthologs.$table_name
            (ortholog_type, protocol_id, ortholog_id, species1, species2, gene_id1, gene_id2
            $prot_id_fields1)
        SELECT
            o1.ortholog_type,
            o1.protocol_id, 
            o1.ortholog_id, 
            o1.species,
            o2.species,
            o1.gene_id,
            o2.gene_id
            $prot_id_fields2
        FROM 
            orthologs.ortholog_sets o1 JOIN
            orthologs.ortholog_sets o2 USING (protocol_id, ortholog_id)
        WHERE 
            o1.ortholog_type ~ 'to' AND 
            o2.ortholog_type ~ 'to' AND
            o1.gene_id < o2.gene_id;
    ANALYSE orthologs.$table_name;
PL/SQLCMD
    print STDERR $sql_cmd;
	$dbh->do($sql_cmd);


	my $index_name = 'i__ortho_pairwise1';
	$index_name .= '_ac' if $prot_id;
	
	# cluster for speed
	$sql_cmd =<<"PL/SQLCMD";
    CLUSTER $index_name ON orthologs.$table_name;
PL/SQLCMD
	 $dbh->do($sql_cmd);
	 #print STDERR $sql_cmd;
}


#_________________________________________________________________________________________
# 
#	Create gene to species temp table
# 
sub create_to_species_temp_table($$)
{
	my ($dbh, $prot_id) = @_;
	print STDERR "\tCreate gene to species temp table\n";

	# table name
	my $suffix			= $prot_id ? 	'_prot_id' : '';
	my $field1			= $prot_id ? 	'prot_id1' : 'gene_id1';
	my $field2			= $prot_id ? 	'prot_id2' : 'gene_id2';

	$sql_cmd =<<"PL/SQLCMD";
    CREATE TEMP TABLE t_gene_to_species$suffix
    (        
        species1       text, 
        species2       text, 
        $field1       text, 
        $field2       text
    );
    
    INSERT INTO t_gene_to_species$suffix
        ( species1, $field1, species2, $field2)
        SELECT
            DISTINCT ON 
            ( species1, $field1, species2, $field2)
            species1, $field1, species2, $field2
        FROM
            orthologs.pairwise$suffix;
            
    CREATE INDEX t_gene_species$suffix on t_gene_to_species$suffix($field1, $field2);
PL/SQLCMD
	$dbh->do($sql_cmd);
	print STDERR $sql_cmd;
}






sub populate_temp_table_summed_by_protocol($$$$$)
{
	my ($dbh, $table_name, $where, $protocol_name, $suffix) = @_;
	my $field1			= length($suffix) ? 'prot_id1' : 'gene_id1';
	my $field2			= length($suffix) ? 'prot_id2' : 'gene_id2';

	$sql_cmd =<<"PL/SQLCMD";
    CREATE TEMP TABLE $table_name$suffix( $protocol_name INT4, $field1 TEXT, $field2 TEXT);
	
    INSERT INTO
        $table_name$suffix ($protocol_name, $field1, $field2)
        SELECT
            sum(protocol_id), 
            $field1,
            $field2
        FROM 
            orthologs.pairwise$suffix
        $where
        GROUP BY
            $field1,
            $field2;
			
    CREATE INDEX i_$table_name$suffix on $table_name$suffix($field1, $field2);
PL/SQLCMD
	$dbh->do($sql_cmd);
	print STDERR $sql_cmd;
}









sub merge_temp_protocol_tables($$)
{
	my ($dbh, $suffix) = @_;
	my $field1			= length($suffix) ? 	'prot_id1' : 'gene_id1';
	my $field2			= length($suffix) ? 	'prot_id2' : 'gene_id2';
	
	$sql_cmd =<<"PL/SQLCMD";
    TRUNCATE TABLE orthologs.pairwise_overlap$suffix;

	VACUUM ANALYSE orthologs.pairwise_overlap$suffix;

	INSERT
        INTO orthologs.pairwise_overlap$suffix
        (
            protocol_id,
            protocol_id_1s,
            protocol_id_manys,
            $field1,
            $field2,
            species1,
            species2
        )
        SELECT
            t1$suffix.protocol_id,
            t2$suffix.protocol_id_1s,
            t3$suffix.protocol_id_manys,
            c.$field1,
            c.$field2,
            c.species1,
            c.species2
        FROM
            t_gene_to_species$suffix c JOIN 
            t1$suffix USING ($field1, $field2) LEFT JOIN
            t2$suffix USING ($field1, $field2) LEFT JOIN
            t3$suffix USING ($field1, $field2);


	VACUUM ANALYSE orthologs.pairwise_overlap$suffix;

	CLUSTER i__ortho_pairwiseover$suffix ON orthologs.pairwise_overlap$suffix;
PL/SQLCMD
	$dbh->do($sql_cmd);
	print STDERR $sql_cmd;
}

	# 
	#	fill pairwise tables
	# 
	print STDERR "\tFill pairwise tables...\n";
	populate_pairwise_relationships($dbh, 0);
	populate_pairwise_relationships($dbh, 1);


	# 
	#	create identifier to species lookup temp table
	# 
	print STDERR "\tCreate identifier to species lookup temp table...\n";
	create_to_species_temp_table($dbh, 0);
	create_to_species_temp_table($dbh, 1);
	


	# 
	#	Populate temp table for all relationships
	# 
	print STDERR "\tPopulate temp table for all relationships...\n";
	my $where = "WHERE ortholog_type ~ 'to'";
	populate_temp_table_summed_by_protocol($dbh, 't1', $where, 'protocol_id', '');
	populate_temp_table_summed_by_protocol($dbh, 't1', $where, 'protocol_id', '_prot_id');
	
	# 
	#	Populate temp table for all 1:1 relationships
	# 
	print STDERR "\tPopulate temp table for 1:1 relationships...\n";
    $where = "WHERE ortholog_type ~ 'to' and NOT ortholog_type ~ 'm'";
	populate_temp_table_summed_by_protocol($dbh, 't2', $where, 'protocol_id_1s', '');
	populate_temp_table_summed_by_protocol($dbh, 't2', $where, 'protocol_id_1s', '_prot_id');

	# 
	#	Populate temp table for all many relationships
	# 
	print STDERR "\tPopulate temp table for all many relationships...\n";
    $where = "WHERE ortholog_type ~ 'to' and ortholog_type ~ 'm'";
	populate_temp_table_summed_by_protocol($dbh, 't3', $where, 'protocol_id_manys', '');
	populate_temp_table_summed_by_protocol($dbh, 't3', $where, 'protocol_id_manys', '_prot_id');

	print STDERR "\tMerge relationships summed by protocol tables back together\n";
	merge_temp_protocol_tables($dbh, '');
	merge_temp_protocol_tables($dbh, '_prot_id');



print STDERR "\tCompleted\n";



log_pipeline_stage($dbh, $curr_protocol_id, 50, 'table pairwise', $start_time);
$dbh->disconnect();





